package com.mms.dao;

import java.math.BigInteger;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.Transaction;

import com.mms.entity.Department;
import com.mms.util.HibernateUtil;

public class DepartmentManagementDao {
	

	
	
	 public List<Object[]> getDepartment() {
	        List<Object[]> departments =new ArrayList<Object[]>();
	        Session session = HibernateUtil.getSession(); 
	        try{ 
	            String sql="select count(staff.de_id) as num, department.de_id,de_name from department left JOIN staff on department.de_id = staff.de_id group by department.de_id";
	            Transaction transaction=session.beginTransaction();
	            Query query=session.createSQLQuery(sql);
	            departments=query.list();
	            transaction.commit();
	        }catch(Exception ex){ 
	            ex.printStackTrace(); 
	        }finally{ 
	        session.close(); 
	        } 
	        return departments;
	    }
	 
	 public boolean AddDepartment(Department department){
		 		Session session = HibernateUtil.getSession();
			try {
				Transaction transaction = session.beginTransaction();
				session.save(department);
				transaction.commit();
				return true;
	        } catch (Exception e) {
	            e.printStackTrace();
	            return false;
	       }finally{
	    	   session.close();
	       }
	}

	
	public boolean ModifyDepartment(Department department){
		 		Session session = HibernateUtil.getSession();
			try {
				Transaction transaction = session.beginTransaction();
				session.update(department);
			    transaction.commit();
			    return true;
	        } catch (Exception e) {
	            e.printStackTrace();
	            return false;
	       }finally{
	    	   session.close();
	       }
	}
	
	public boolean DeleteDepartment(Department department){
			
			Session session = HibernateUtil.getSession();
		try{
			Transaction transaction = session.beginTransaction();
			session.delete(department);
			transaction.commit();
			return true;
		}catch(Exception e){
			e.printStackTrace();
			return false;
		}finally{
			session.close();
		}
	}
	
	 public List<Department> getDepartments() {
	       	Session session = HibernateUtil.getSession();
	       	List<Object[]> objs;
	        List<Department> departments =new ArrayList<Department>();
	        try {
	            String sql="select * from department";
	            Transaction transaction = session.beginTransaction();
	            Query query = session.createSQLQuery(sql);
	            objs = query.list();
	            for (Object[] objects : objs) {
	                Department department =new Department();
	                department.setDeId(Integer.parseInt(objects[0].toString()));
	                department.setDeName(objects[1].toString());
	                departments.add(department);
	            }
	            transaction.commit();
	        } catch (Exception e) {
	            e.printStackTrace();
	        }finally{
	        	session.close();
	        }
	        return departments;
	    }
	 
	 public List<Department> getDepCount(int Spage) {
	        	Session session = HibernateUtil.getSession();
	        	List<Department> departments = new ArrayList<Department>();
	        	List<Object[]> objs;
	        try {
	            String sql="select count(staff.de_id) as num, department.de_id,de_name from department left JOIN staff on department.de_id = staff.de_id group by department.de_id limit ?,6";
	            Transaction transaction = session.beginTransaction();
	            Query query = session.createSQLQuery(sql);
	            query.setInteger(0,Spage*6);
	            objs = query.list();
	            for (Object[] objects : objs) {
	                Department department =new Department();
	                department.setNum(Integer.parseInt(objects[0].toString()));
	                department.setDeId(Integer.parseInt(objects[1].toString()));
	                department.setDeName(objects[2].toString());
	                departments.add(department);
	            }
	            transaction.commit();
	        } catch (Exception e) {
	            e.printStackTrace();
	        }
	        return departments;
	    }
	 
	 public int getcount(){
		 Session session = HibernateUtil.getSession();
		 int nums = 0;
		 List<BigInteger> num;
		 try{
			 String sql = "select  count(de_id) from department";
			 Transaction transaction = session.beginTransaction();
			 Query query = session.createSQLQuery(sql);
			 num = query.list();
			 nums = Integer.parseInt(num.get(0).toString());
			 transaction.commit();
		 }catch(Exception e){
			 e.printStackTrace();
		 }finally{
			 session.close();
		 }
		 return nums;
	 }
	
}
